﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Reflection;
using System.Text.RegularExpressions;
using Vbyte.SharpOrm.Config;
using Vbyte.SharpOrm.SqlDb;
using Vbyte.SharpOrm.Adapter;
using Vbyte.DataSource.Cache;

namespace Vbyte.SharpOrm
{
    public static partial class OrmHelper
    {
        #region private 使用
        private static void AddParameter(DbCommand cmd, string paramName, object paramValue, int? size)
        {
            DbParameter p = cmd.CreateParameter();
            p.ParameterName = paramName;
            if (size.HasValue)  p.Size = size.Value;

            if (paramValue == null)
            {
                p.SourceColumnNullMapping = true;
                p.Value = null;
            }
            else
            {
                p.Value = paramValue;
            }
            cmd.Parameters.Add(p);
        }

        /// <summary>
        /// 获取为ORM项配置所作的适配器
        /// </summary>
        /// <param name="sqlTableEntry">表对象映射实例</param>
        /// <param name="tableName">所映射的数据库表名</param>
        /// <returns></returns>
        private static AdoNetAdapter GetOrmAdapter(TableEntry sqlTableEntry, out string tableName)
        { 
            BindTableAttribute[] bdTabAttrs = GetInstanceCustomAttributes<BindTableAttribute>(sqlTableEntry.GetType(), true);
            if (bdTabAttrs.Length < 1)
            {
                throw new InvalidOperationException("指定对象没有配置BindTableAttribute属性！");
            }
            else
            {
                tableName = bdTabAttrs[0].PrimaryTableName;
                return new AdoNetAdapter(bdTabAttrs[0].BindConnectionKey);
            }
        }
        #endregion

        #region DbCommand 生成

        /// <summary>
        /// (主键识别)生成单表单行的数据操作命令
        /// </summary>
        /// <param name="SqlTableEntry">表对象映射实例，该对象原型有<see cref="BindTableAttribute"/>特性标记。</param>
        /// <param name="cType">命令类型</param>
        /// <returns>数据库相关操作命令</returns>
        public static DbCommand GeneratorDbCommand(this TableEntry SqlTableEntry, SQLRowCommandType cType)
        {
            return GeneratorDbCommand(SqlTableEntry, cType, null, null);
        }

        /// <summary>
        /// 生成单表单行的数据操作命令
        /// </summary>
        /// <param name="SqlTableEntry">表对象映射实例，该对象原型有<see cref="BindTableAttribute"/>特性标记。</param>
        /// <param name="cType">命令类型</param>
        /// <param name="NotIgnoreDefaultValueProperties">强制不忽略默认值的字段集合</param>
        /// <returns>数据库相关操作命令</returns>
        public static DbCommand GeneratorDbCommand(this TableEntry SqlTableEntry, SQLRowCommandType cType,
            string[] NotIgnoreDefaultValueProperties)
        {
            return GeneratorDbCommand(SqlTableEntry, cType, NotIgnoreDefaultValueProperties, null);
        }

        /// <summary>
        /// 生成单表单行的数据操作命令
        /// </summary>
        /// <param name="SqlTableEntry">表对象映射实例，该对象原型有<see cref="BindTableAttribute"/>特性标记。</param>
        /// <param name="cType">命令类型</param>
        /// <param name="NotIgnoreDefaultValueProperties">强制不忽略默认值的字段集合</param>
        /// <param name="selectFields">只选取的字段集合</param>
        /// <returns>数据库相关操作命令</returns>
        public static DbCommand GeneratorDbCommand(this TableEntry SqlTableEntry, SQLRowCommandType cType,
            string[] NotIgnoreDefaultValueProperties,
            string[] selectFields)
        {
            return GeneratorDbCommand(SqlTableEntry, cType, NotIgnoreDefaultValueProperties, selectFields, null);
        }

        /// <summary>
        /// 生成单表单行的数据操作命令
        /// </summary>
        /// <param name="SqlTableEntry">表对象映射实例，该对象原型有<see cref="BindTableAttribute"/>特性标记。</param>
        /// <param name="cType">命令类型</param>
        /// <param name="NotIgnoreDefaultValueProperties">强制不忽略默认值的字段集合</param>
        /// <param name="selectFields">只选取的字段集合</param>
        /// <param name="OrderSettings">针对提取数据的排序设置</param>
        /// <returns>生成相关操作的SQL语句，待优化。</returns>
        public static DbCommand GeneratorDbCommand(this TableEntry SqlTableEntry, SQLRowCommandType cType,
            string[] NotIgnoreDefaultValueProperties, string[] selectFields, SqlOrderBy[] OrderSettings)
        {
            ISqlBuilderAdapter builder = null;
            return GeneratorDbCommandWithBuilder(SqlTableEntry, cType, NotIgnoreDefaultValueProperties, selectFields, OrderSettings, out builder);
        }

        /// <summary>
        /// 生成单表单行的数据操作命令，并输出SQL构建适配器实例。
        /// </summary>
        /// <param name="SqlTableEntry">表对象映射实例，该对象原型有<see cref="BindTableAttribute"/>特性标记。</param>
        /// <param name="cType">命令类型</param>
        /// <param name="NotIgnoreDefaultValueProperties">强制不忽略默认值的字段集合</param>
        /// <param name="selectFields">只选取的字段集合</param>
        /// <param name="OrderSettings">针对提取数据的排序设置</param>
        /// <param name="BuilderInstance">输出的SQL构建适配器</param>
        /// <returns>生成相关操作的SQL语句，待优化。</returns>
        public static DbCommand GeneratorDbCommandWithBuilder(this TableEntry SqlTableEntry, SQLRowCommandType cType,
            string[] NotIgnoreDefaultValueProperties, string[] selectFields, SqlOrderBy[] OrderSettings, out ISqlBuilderAdapter BuilderInstance)
        { 
            Type t = SqlTableEntry.GetType(); 
            AdoNetAdapter ormAdp = new AdoNetAdapter(SqlTableEntry);
            DbCommand cmd = ormAdp.GetDbCommand();
            BindTableAttribute bTabAttr = ormAdp.GetTableBindConfig();
            ISqlBuilderAdapter Builder = ormAdp.GetSqlBuilder();
            ormAdp.Dispose();

            //从缓存获取表定义
            SqlTable TabDefine = EntryTableCache.GetTableCache(SqlTableEntry, Builder);

            //对象约束与筛选配置
            ISqlConstrains constrains = SqlTableEntry.GetConstrains();

            #region 设置数据表、关联表
            if (cType != SQLRowCommandType.Select)
            {
                Builder.VirtualTable = bTabAttr.PrimaryTableName;
            }
            else
            {
                //仅对获取数据指定关联表
                if (bTabAttr.IsSingleTableBind())
                {
                    Builder.VirtualTable = bTabAttr.PrimaryTableName;
                }
                else
                {
                    Builder.VirtualTable = bTabAttr.ToSqlCollectionTable(SqlTableEntry);
                }
            }
            #endregion

            string propName = "";
            object propValue = null;

            //属性值设置Name=@Name
            List<string> listValueBind = new List<string>();
            //字段列表 ID,Name,...
            List<string> listFieldsList = new List<string>();
            //字段值列表 @ID,@Name...
            List<string> listValuesList = new List<string>();

            List<string> swapList = new List<string>();
            List<string> swapList2 = new List<string>();

            bool isSingleRowSelect = false; //是否单行选取
            bool blnUpdateByBind = false;   //是否通过实体属性数据绑定作为更新条件
            PropertyInfo[] Properties = GenericCache<PropertyInfo[]>.Get(t.FullName, () => t.GetProperties());
            foreach (PropertyInfo pi in Properties)
            {
                #region 数组属性忽略
                if (pi.PropertyType.IsArray && pi.PropertyType != typeof(byte[])) continue;
                #endregion

                propName = pi.Name;
                propValue = pi.GetGetMethod().Invoke(SqlTableEntry, null);
                bool itemMapping = false;

                #region 忽略属性
                if (HasAttribute(pi, typeof(CommandIgnoreAttribute), true))
                {
                    continue;
                }

                //导入忽略
                if (cType == SQLRowCommandType.Insert || cType == SQLRowCommandType.Update)
                {
                    if (HasAttribute(pi, typeof(ImportIgnoreAttribute), true))
                    {
                        continue;
                    }
                }
                #endregion

                #region 获取基本配置

                //标记有映射转换
                TableItemAttribute[] itemAttr = GetMemberInfoCustomAttributes<TableItemAttribute>(pi, true);
                if (itemAttr.Length > 0)
                {
                    //多表联接仅应用于Select
                    if (bTabAttr.IsSingleTableBind() || cType != SQLRowCommandType.Select)
                    {
                        propName = itemAttr[0].BindField;
                    }
                    else
                    {
                        #region 添加表别名前缀
                        if (string.IsNullOrEmpty(itemAttr[0].BindTable))
                        {
                            propName = bTabAttr.GetTableAliasName(bTabAttr.PrimaryTableName) + "." + itemAttr[0].BindField;
                        }
                        else
                        {
                            propName = bTabAttr.GetTableAliasName(itemAttr[0].BindTable) + "." + itemAttr[0].BindField;
                        }
                        #endregion
                    }

                    itemMapping = true;
                }
                else
                {
                    //多表联接添加别名前缀
                    if (!bTabAttr.IsSingleTableBind() && cType == SQLRowCommandType.Select)
                    {
                        propName = bTabAttr.GetTableAliasName(bTabAttr.PrimaryTableName) + "." + pi.Name;
                    }
                }

                bool addToCmdParameter = false;  //标记是否添加参数

                bool isFixedValueColumn = false; //标记数据列是否允许修改
                PrimaryKeyAttribute[] PKAttr = GetMemberInfoCustomAttributes<PrimaryKeyAttribute>(pi, true);
                //主键数量为1或为自动增加键，一般地主键数量为1时，默认为自动增加键。
                if (PKAttr.Length > 0 && TabDefine.PrimaryKeys.Length == 1)
                {
                    PrimaryKeyAttribute KAttr = PKAttr[0];
                    if (!KAttr.InsertAble) isFixedValueColumn = true;
                }
                else
                {
                    isFixedValueColumn = HasAttribute(pi, typeof(AutoIncrementAttribute), true);
                }

                //Console.WriteLine("{0}:{1}", pi.Name, isFixedValueColumn);

                bool blnCurrentBindDefault = IsInstanceBindDefaultValue(pi.PropertyType, propValue);
                if (isFixedValueColumn && !blnCurrentBindDefault) addToCmdParameter = true;

                //检查原始属性或映射属性在强制加入列表，去掉别名前缀
                bool blnMustAdd = false;
                if (NotIgnoreDefaultValueProperties != null && NotIgnoreDefaultValueProperties.Length > 0)
                {
                    string aliasPrefixPattern = "^(([\\w_\\-\\[\\]\\d]+)\\.)";
                    blnMustAdd = StringArrayContains(NotIgnoreDefaultValueProperties, Regex.Replace(propName, aliasPrefixPattern, ""));
                    if (itemMapping == true)
                    {
                        blnMustAdd = StringArrayContains(NotIgnoreDefaultValueProperties, Regex.Replace(pi.Name, aliasPrefixPattern, ""));
                    }
                }
                #endregion

                //Console.WriteLine("{0}:{1}, must add: {2}", pi.Name, addToCmdParameter, blnMustAdd);
                //Console.WriteLine("{0}:fixed {1}", pi.Name, isFixedValueColumn);
                #region 如果是主键，则参数名称从数据字段名称(待优化)

                if (cType == SQLRowCommandType.Insert)
                {
                    #region Insert
                    if (!isFixedValueColumn && (!blnCurrentBindDefault || blnMustAdd))
                    {
                        //Console.WriteLine("{0} added, fixed:{1}", propName, isFixedValueColumn);
                        listFieldsList.Add(propName);
                        listValuesList.Add("@" + pi.Name);
                        addToCmdParameter = true;
                    }
                    else
                    {
                        addToCmdParameter = false;
                    }
                    #endregion
                }
                else if (cType == SQLRowCommandType.Select)
                {
                    #region Select
                    if (isFixedValueColumn)
                    {
                        if (!blnCurrentBindDefault)
                        {
                            listValueBind.Add(string.Format("{0}=@{1}", propName, propName));
                            addToCmdParameter = true;
                            isSingleRowSelect = true;
                        }
                    }
                    else
                    {
                        if (!blnCurrentBindDefault)
                        {
                            if (!HasAttribute(pi, typeof(LinkIgnoreAttribute), true))
                            {
                                listValueBind.Add(string.Format("{0}=@{1}", propName, pi.Name));
                                addToCmdParameter = true;
                            }
                        }
                        else
                        {
                            if (blnMustAdd)
                            {
                                listValueBind.Add(string.Format("{0}=@{1}", propName, pi.Name));
                                addToCmdParameter = true;
                            }
                        }
                    }

                    //System.Diagnostics.Debug.WriteLine(propName);

                    if (selectFields != null && selectFields.Length > 0
                            && string.Join("", selectFields).Trim() != string.Empty)
                    {
                        //字段列表
                        if (StringArrayContains(selectFields, propName) || StringArrayContains(selectFields, pi.Name))
                        {
                            listFieldsList.Add(propName);
                        }
                    }
                    else
                    {
                        //选择所有满足条件的记录
                        listFieldsList.Add(propName);
                    }
                    #endregion
                }
                else if (cType == SQLRowCommandType.Update)
                {
                    #region Update
                    if (isFixedValueColumn)
                    {
                        if (blnCurrentBindDefault) blnUpdateByBind = true;
                    }
                    else
                    {
                        if (!blnCurrentBindDefault || blnMustAdd)
                        {
                            listValueBind.Add(string.Format("{0}=@{1}", propName, pi.Name));
                            addToCmdParameter = true;
                        }
                    }
                    #endregion
                }
                else //delete
                {
                    #region Delete
                    if (isFixedValueColumn)
                    {
                        if (!blnCurrentBindDefault)
                        {
                            listValueBind.Add(string.Format("{0}=@{1}", propName, propName));
                            addToCmdParameter = true;
                        }
                    }
                    else
                    {
                        if (!blnCurrentBindDefault || blnMustAdd)
                        {
                            listValueBind.Add(string.Format("{0}=@{1}", propName, pi.Name));
                            addToCmdParameter = true;
                        }
                    }
                    #endregion
                }

                #endregion

                #region 添加输入参数
                if (addToCmdParameter)
                {
                    SqlTableColumn col = TabDefine[propName];
                    if (col != null && col.MaxLength.HasValue)
                    {
                        AddParameter(cmd, (isFixedValueColumn) ? propName : pi.Name,
                            propValue, col.MaxLength);
                    }
                    else
                    {
                        AddParameter(cmd, (isFixedValueColumn) ? propName : pi.Name, propValue, null);
                    }
                }
                #endregion

            }

            #region {FieldsList}
            //判定是否增加了自定义筛选列
            if (constrains != null)
            {
                swapList = constrains.GetCustomFieldList();
                if (swapList.Count > 0)  listFieldsList.AddRange(swapList);
            }
            if (listFieldsList.Count > 0)  Builder.FieldNamesList = listFieldsList.ToArray();
            #endregion

            #region {ValueList}
            if (constrains != null)
            {
                swapList = constrains.GetCustomValueList();
                if (swapList.Count > 0)  listValuesList.AddRange(swapList);
            }
            if (listValuesList.Count > 0)  Builder.FieldValuesList = listValuesList.ToArray();
            #endregion

            #region (更新)判定是否构建 {ValueSetList}
            if (cType == SQLRowCommandType.Update)
            {
                if (listValueBind.Count < 1)
                {
                    if (constrains == null)
                    {
                        throw new InvalidOperationException("没有设置更新项！");
                    }
                    else
                    {
                        #region 通过自定义字段和字段值设置更新
                        swapList = constrains.GetCustomFieldList();
                        swapList2 = constrains.GetCustomValueList();
                        if (swapList.Count != swapList2.Count || swapList.Count < 1)
                        {
                            throw new InvalidOperationException("字段数量与字段值数量设置不一致，或没有设置需要更新的字段！");
                        }
                        else
                        {
                            //通过自定义字段和字段值生成更新语句
                            for (int s = 0; s < swapList.Count; s++)
                            {
                                swapList[s] = string.Format("{0}={1}", swapList[s], swapList2[s]);
                            }
                            Builder.CustomValueSetList = swapList.ToArray();
                        }
                        #endregion
                    }
                }
                else
                {
                    Builder.CustomValueSetList = listValueBind.ToArray();
                }
            }
            #endregion

            #region 配置限制条件
            //Debug.WriteLine(listValueBind.Count);
            if (listValueBind.Count > 0)
            {
                if (cType == SQLRowCommandType.Update && blnUpdateByBind == false)
                {
                    string[] listMultiKeys = new string[TabDefine.PrimaryKeys.Length];
                    for (int i = 0; i < TabDefine.PrimaryKeys.Length; i++)
                    {
                        listMultiKeys[i] = TabDefine.PrimaryKeys[i] + " = @" + TabDefine.PrimaryKeys[i];
                    }

                    if (listMultiKeys.Length > 0)
                    {
                        Builder.SqlCondition = " WHERE " + string.Join(" AND ", listMultiKeys);
                    }
                    else
                    {
                        #region 多个标志键限制
                        listMultiKeys = new string[TabDefine.IdentityKeys.Length];
                        if (TabDefine.IdentityKeys.Length > 0)
                        {
                            for (int k = 0; k < TabDefine.IdentityKeys.Length; k++)
                            {
                                listMultiKeys[k] = TabDefine.IdentityKeys[k] + " = @" + TabDefine.IdentityKeys[k];
                            }
                            Builder.SqlCondition = " WHERE " + string.Join(" AND ", listMultiKeys);
                        }
                        #endregion
                    }
                }
                else
                {
                    Builder.SqlCondition = "WHERE " + string.Join(" AND ", listValueBind.ToArray());
                }
            }

            if (constrains != null)
            {
                string[] DictItem = constrains.GetConditions();
                if (DictItem.Length > 0)
                {
                    if (Builder.SqlCondition != null && Builder.SqlCondition.Trim() != string.Empty)
                    {
                        Builder.SqlCondition += " AND " + string.Join(" ", DictItem);
                    }
                    else
                    {
                        Builder.SqlCondition = "WHERE " + string.Join(" ", DictItem);
                    }
                }
            }
            #endregion

            #region Group By
            if (constrains != null && constrains.GetGroupByConfig().Length > 0)
            {
                if (Builder.SqlGroupBy.Trim() != string.Empty)
                {
                    Builder.SqlGroupBy += string.Join(" ", constrains.GetGroupByConfig()); 
                }
                else
                {
                    Builder.SqlGroupBy = "Group By " + string.Join(" ", constrains.GetGroupByConfig()); 
                }
            }
            #endregion

            #region 配置排序条件
            if (OrderSettings != null && OrderSettings.Length > 0)
            {
                string[] sqlOrder = Array.ConvertAll<SqlOrderBy, string>(OrderSettings, (new Converter<SqlOrderBy, string>(delegate(SqlOrderBy orderBy)
                {
                    string dbfieldName = orderBy.FieldName;
                    PropertyInfo pi = t.GetProperty(orderBy.FieldName);
                    if (pi != null)
                    {
                        TableItemAttribute[] itemAttr = GetMemberInfoCustomAttributes<TableItemAttribute>(pi, true);
                        if (itemAttr.Length > 0)
                        {
                            dbfieldName = itemAttr[0].BindField;
                        }
                    }
                    return string.Format("{0} {1}", dbfieldName, orderBy.Direction == SqlOrderByDirection.ASC ? "ASC" : "DESC");
                })));

                Builder.SqlOrderBy = "Order By " + string.Join(", ", sqlOrder);
            }
            #endregion

            #region 最终的命令文本及验证
            string cmdTxt = Builder.BuildCommandText(SqlTableEntry, cType, isSingleRowSelect);
            if (!VerifyCommandText(cmdTxt, cType))
            {
                throw new InvalidOperationException("文本命令构建不正确，当前文本命令为" + cmdTxt);
            }
            else
            {
                cmd.CommandText = cmdTxt;
            }
            #endregion

            #region 配置信息是否开启记录SQL语句操作
            //if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["SharpOrm.LogCommandText"]) && Convert.ToBoolean(ConfigurationManager.AppSettings["SharpOrm.LogCommandText"]))
            //{
            //    //按日文件夹的每小时记录一个文件文件
            //    Common.Log("/ORM/" + DateTime.Now.ToString("yyyy-MM-dd.HH").Replace(".", "/"),
            //        !string.IsNullOrEmpty(ConfigurationManager.AppSettings["SharpOrm.LogStackTrace"]) && Convert.ToBoolean(ConfigurationManager.AppSettings["SharpOrm.LogStackTrace"]),
            //        sw =>
            //     {
            //         sw.WriteLine(cmd.CommandText);
            //         foreach (DbParameter p in cmd.Parameters)
            //         {
            //             sw.WriteLine("@" + p.ParameterName + " = " + p.Value);
            //         }
            //         sw.WriteLine();
            //         sw.WriteLine();
            //     });
            //}
            #endregion

            BuilderInstance = Builder;
            return cmd;
        }

        /// <summary>
        /// 内部验证文本命令
        /// </summary>
        /// <param name="cmdTxt">命令文本</param>
        /// <param name="cType">行命令类型</param>
        /// <returns>返回是否是有效的命令文本</returns>
        private static bool VerifyCommandText(string cmdTxt, SQLRowCommandType cType)
        {
            bool result = true;
            if (cType == SQLRowCommandType.Insert)
            {
                //{Table}({FieldsList}) values({ValueList})
                //防止字段和属性都为空内容
                result = !Regex.IsMatch(cmdTxt, "[\\w\\]]+\\(\\)\\s+values\\(\\)", RegexOptions.IgnoreCase);
            }
            return result;
        }

        #endregion
    }
}
